/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package MSSQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.HashMap;



public class DBInitialize {
    static{
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }catch(Exception ex){System.out.println(ex);}
        }
    Connection getConnection()throws Exception
    {
        java.sql.Connection cnn=
                DriverManager.getConnection("jdbc:sqlserver://114.32.112.40:168;databasename=NorthWind"
                        , "leagueofpg", "gjun");
        return cnn;
    }
    
    public int modifly(String sqlstatement,String parameter[]) throws Exception
    {
        int x=0;
        Connection cnn=null;
        
        try
        {
            cnn=getConnection();
            java.sql.PreparedStatement ps = null;
            try
            {
                ps=cnn.prepareStatement(sqlstatement);
                for(int i=0;i<parameter.length;i++)
                    ps.setString(i+1,parameter[i]);
                x=ps.executeUpdate();
            }
            finally
            {
                ps.close();
            }
            
        }
        finally{
            cnn.close();
        }
        
        return x;
    }
    
    public ArrayList<HashMap> query(String sqlstatement,String parameter[])throws Exception
    {
        ArrayList<HashMap> rslist=new ArrayList<HashMap>();
        Connection cnn=null;
        
        try
        {
            cnn=getConnection();
            java.sql.PreparedStatement ps = null;
            try
            {
                ps=cnn.prepareStatement(sqlstatement);
                for(int i=0;i<parameter.length;i++)
                    ps.setString(i+1,parameter[i]);
                ResultSet rs=null;
                try
                {
                    rs=ps.executeQuery();
                    java.sql.ResultSetMetaData meta=rs.getMetaData();
                    int count=meta.getColumnCount();
                    
                    while(rs.next())
                    {
                        HashMap hs = new HashMap();
                        for(int i=1;i<=count;i++)
                        {
                            String key=meta.getColumnName(i);
                            String value=rs.getString(i);
                            hs.put(key,value);
                        }
                        rslist.add(hs);
                    }
                    
                }
                finally
                {
                    rs.close();
                }
                
            }
            finally
            {
                ps.close();
            }
            
        }
        finally{
            cnn.close();
        }
        
        return rslist;
    }
    
    
    
    public ArrayList<HashMap> query(String sqlstatement,String parameter[],int index ,int section)throws Exception
    {
        ArrayList<HashMap> rslist=new ArrayList<HashMap>();
        Connection cnn=null;
        
        try
        {
            cnn=getConnection();
            java.sql.PreparedStatement ps = null;
            
            try
            {
                ps=cnn.prepareStatement(sqlstatement+" OFFSET "+index+" ROWS FETCH NEXT "+section+" ROWS ONLY");
                for(int i=0;i<parameter.length;i++)
                    ps.setString(i+1,parameter[i]);
                ResultSet rs=null;
                try
                {
                    rs=ps.executeQuery();
                    java.sql.ResultSetMetaData meta=rs.getMetaData();
                    int count=meta.getColumnCount();
                    
                    while(rs.next())
                    {
                        HashMap hs = new HashMap();
                        for(int i=1;i<=count;i++)
                        {
                            String key=meta.getColumnName(i);
                            String value=rs.getString(i);
                            hs.put(key,value);
                        }
                        rslist.add(hs);
                    }
                    
                }catch(Exception ex)
                {
                    System.out.println(ex);
                }
                finally
                {
                    rs.close();
                }
                
            }
            finally
            {
                ps.close();
            }
            
        }
        finally{
            cnn.close();
        }
        
        return rslist;
    }
    
    public int getTotalCount(String Table)throws Exception
    {
        int count=-1;
        Connection cnn=getConnection();
        java.sql.Statement st=cnn.createStatement();
        ResultSet set=st.executeQuery("select COUNT(*) as [count] FROM "+Table);
        if(set.next())
            count=Integer.parseInt(set.getString("count"));
        cnn.close();
        return count;
    }
    
        


}

